#!/usr/bin/python
# -*- coding: UTF-8 -*-
import psycopg2
import psycopg2.extras
import sqlite3
from flaskr.CommonUtils.ConfigUtil import *
from flaskr.CommonUtils.CommonTool import *
import json


class DBUtils:
    """
    Python 连接 其他数据库的工具
    """

    def __init__(self):
        # 属性字典（表字段、需要提供的数据）
        self.__value = {
            "connect_name": "",
            "database_type": "",
            "host": "",  # 主机IP
            "port": "",
            "dbname": "",
            "username": "",
            "password": ""
        }

    @property
    def value(self):
        return self.__value

    @value.setter
    def value(self, value: dict):
        self.__value = value

    @value.deleter
    def value(self):
        del self.__value

    def init_gauss_or_pg_data(self, table_name_i: str, sql_string_i: str):
        result = self.exec_sql_get_result_from_gauss_or_pg(sql_string_i)
        print(str(result))
        DBUtils.insert_sqlite3_table_from_data_list(table_name_i, result)

    # 获取 pg 或者 openGauss 的 cursor
    def get_cursor(self):
        dbname = str(self.__value["dbname"])  # db_info_dict["dbname"]
        username = str(self.__value["username"])  # db_info_dict["username"]
        password = str(self.__value["password"])  # db_info_dict["password"]
        host = str(self.__value["host"])  # db_info_dict["host"]
        port = str(self.__value["port"])  # db_info_dict["port"]
        connect_string = "dbname=" + dbname + " user=" + username + " password=" + password + " host=" + host \
                         + " port=" + port
        print("connect_string = " + connect_string)
        # 连接到数据库
        conn = psycopg2.connect(connect_string)
        cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
        return cursor

    # 连接数据库执行脚本
    def conn_db_exec_sql_from_dict(self, sql_script_i: str):
        dbname = str(self.__value["dbname"])  # db_info_dict["dbname"]
        username = str(self.__value["username"])  # db_info_dict["user"]
        password = str(self.__value["password"])  # db_info_dict["password"]
        host = str(self.__value["host"])  # db_info_dict["host"]
        port = str(self.__value["port"])  # db_info_dict["port"]
        connect_string = "dbname=" + dbname + " user=" + username + " password=" + password + " host=" + host \
                         + " port=" + port
        print(connect_string)
        # 连接到数据库
        conn = psycopg2.connect(connect_string)
        cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
        # 执行SQL命令
        cursor.execute(sql_script_i)
        # 提交SQL命令
        conn.commit()
        conn.close()

    # 连接 gauss 或者 pg 数据库读取表数据，返回结果
    def exec_sql_get_result_from_gauss_or_pg(self, sql_script_i: str):
        dbname = str(self.__value["dbname"])  # db_info_dict["dbname"]
        username = str(self.__value["username"])  # db_info_dict["user"]
        password = str(self.__value["password"])  # db_info_dict["password"]
        host = str(self.__value["host"])  # db_info_dict["host"]
        port = str(self.__value["port"])  # db_info_dict["port"]
        connect_string = "dbname=" + dbname + " user=" + username + " password=" + password + " host=" + host \
                         + " port=" + port
        print("connect_string = " + connect_string)
        # 连接到数据库
        conn = psycopg2.connect(connect_string)
        cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
        cursor.execute(sql_script_i)
        rows = cursor.fetchall()
        # 转换成字典
        tmp = []
        for item in rows:
            tmp.append(dict(item))
        # print("tmp = " + str(tmp))
        return tmp

    # 连接数据库，把list数据写入SQLite3的表中
    @staticmethod
    def insert_sqlite3_table_from_data_list(table_name_i, data_list_i: list):
        database = ConfigUtil.get_sqlite3_path()
        conn = sqlite3.connect(database, detect_types=sqlite3.PARSE_DECLTYPES)
        cursor = conn.cursor()
        for item in data_list_i:
            keys = ','.join(item.keys())
            # values = ','.join(['%s'] * len(item))
            t = tuple(item.values())
            tmp_tuple = tuple('' if x is None else x for x in t)
            values = str(tmp_tuple)
            sql = 'INSERT INTO %s (%s) VALUES %s' % (table_name_i, keys, values)
            # cursor = self.get_cursor()
            print("sql = " + sql)
            print("tuple(item.values()) = " + str(tuple(item.values())))
            cursor.execute(sql, )
        conn.commit()
        # 关闭连接
        conn.close()

    # 通用 pg 或 高斯
    def common_insert_table_from_data_list(self, table_name_i, data_list_i: list):
        # database = ConfigUtil.get_sqlite3_path()
        # conn = sqlite3.connect(database, detect_types=sqlite3.PARSE_DECLTYPES)
        # cursor = conn.cursor()

        connect_name = str(self.__value["connect_name"])
        dbname = str(self.__value["dbname"])
        username = str(self.__value["username"])
        password = str(self.__value["password"])
        host = str(self.__value["host"])
        port = str(self.__value["port"])
        connect_string = "dbname=" + dbname + " user=" + username + " password=" + password + " host=" + host \
                         + " port=" + port
        print("connect_string = " + connect_string)
        # 连接到数据库
        conn = psycopg2.connect(connect_string)
        cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
        cursor.execute("delete from " + table_name_i + " where source = '" + connect_name + "'", )
        for item in data_list_i:
            keys = ','.join(item.keys())
            # values = ','.join(['%s'] * len(item))
            print("item = " + str(item))
            print("type(item) = " + str(type(item)))
            print("item.values() = " + str(item.values()))

            t = tuple(item.values())
            print("t = " + str(t))
            tmp_tuple = tuple('' if x is None else x for x in t)
            print("tmp_tuple = " + str(tmp_tuple))

            # 将元组转换为列表
            list_data = list(tmp_tuple)

            # 把list里面的元素有单引号的替换为两个单引号
            list_data = [item.replace("'", "''") for item in list_data]
            # 把list里面的元素首尾添加一个单引号
            list_data = [f"'{item}'" for item in list_data]

            # 使用join方法将列表转换为字符串
            string_result = ','.join(list_data)
            string_result = "(" + string_result + ")"
            values = string_result
            print("string_result = " + str(string_result))

            # values = str(tmp_tuple).replace("\"", "'")
            print("values = " + values)
            sql = 'INSERT INTO %s (%s) VALUES %s' % (table_name_i, keys, values)
            # cursor = self.get_cursor()
            print("sql = " + sql)
            # print("tuple(item.values()) = " + str(tuple(item.values())))
            cursor.execute(sql, )
        conn.commit()
        # 关闭连接
        conn.close()

    # 通用 pg 或 高斯
    def common_insert_table_from_data_list_no_delete(self, table_name_i, data_list_i: list):
        # database = ConfigUtil.get_sqlite3_path()
        # conn = sqlite3.connect(database, detect_types=sqlite3.PARSE_DECLTYPES)
        # cursor = conn.cursor()

        connect_name = str(self.__value["connect_name"])
        dbname = str(self.__value["dbname"])
        username = str(self.__value["username"])
        password = str(self.__value["password"])
        host = str(self.__value["host"])
        port = str(self.__value["port"])
        connect_string = "dbname=" + dbname + " user=" + username + " password=" + password + " host=" + host \
                         + " port=" + port
        print("connect_string = " + connect_string)
        # 连接到数据库
        conn = psycopg2.connect(connect_string)
        cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
        # cursor.execute("delete from " + table_name_i + " where source = '" + connect_name + "'", )
        for item in data_list_i:
            keys = ','.join(item.keys())
            # values = ','.join(['%s'] * len(item))
            print("item = " + str(item))
            print("type(item) = " + str(type(item)))
            print("item.values() = " + str(item.values()))

            t = tuple(item.values())
            print("t = " + str(t))
            tmp_tuple = tuple('' if x is None else x for x in t)
            print("tmp_tuple = " + str(tmp_tuple))

            # 将元组转换为列表
            list_data = list(tmp_tuple)

            # 把list里面的元素有单引号的替换为两个单引号
            list_data = [item.replace("'", "''") for item in list_data]
            # 把list里面的元素首尾添加一个单引号
            list_data = [f"'{item}'" for item in list_data]

            # 使用join方法将列表转换为字符串
            string_result = ','.join(list_data)
            string_result = "(" + string_result + ")"
            values = string_result
            print("string_result = " + str(string_result))

            # values = str(tmp_tuple).replace("\"", "'")
            print("values = " + values)
            sql = 'INSERT INTO %s (%s) VALUES %s' % (table_name_i, keys, values)
            # cursor = self.get_cursor()
            print("sql = " + sql)
            # print("tuple(item.values()) = " + str(tuple(item.values())))
            cursor.execute(sql, )
        conn.commit()
        # 关闭连接
        conn.close()

    # 连接数据库，把list数据写入SQLite3的表中，单条数据
    @staticmethod
    def insert_sqlite3_table_from_data_single(table_name_i, data_list_i: list):
        database = ConfigUtil.get_sqlite3_path()
        conn = sqlite3.connect(database, detect_types=sqlite3.PARSE_DECLTYPES)
        cursor = conn.cursor()
        for item in data_list_i:
            keys = ','.join(item.keys())
            # values = ','.join(['%s'] * len(item))
            t = tuple(item.values())
            tmp_tuple = tuple('' if x is None else x for x in t)
            values = str(tmp_tuple)
            sql = 'INSERT INTO %s (%s) VALUES %s' % (table_name_i, keys, values)
            # cursor = self.get_cursor()
            print("sql = " + sql)
            print("tuple(item.values()) = " + str(tuple(item.values())))
            cursor.execute(sql, )
        conn.commit()
        # 关闭连接
        conn.close()

    # 执行 sqlite3 数据库的脚本
    @staticmethod
    def execute_sqlite3_scripts(sql_i: str):
        database = ConfigUtil.get_sqlite3_path()
        conn = sqlite3.connect(database, detect_types=sqlite3.PARSE_DECLTYPES)
        cursor = conn.cursor()
        cursor.execute(sql_i, )
        conn.commit()
        # 关闭连接
        conn.close()

    # 检查数据库是否能连接成功
    def check_database_connection(self):
        database_type = str(self.__value["database_type"])
        dbname = str(self.__value["dbname"])  # db_info_dict["dbname"]
        username = str(self.__value["username"])  # db_info_dict["username"]
        password = str(self.__value["password"])  # db_info_dict["password"]
        host = str(self.__value["host"])  # db_info_dict["host"]
        port = str(self.__value["port"])  # db_info_dict["port"]
        connect_string = "dbname=" + dbname + " user=" + username + " password=" + password + " host=" + host \
                         + " port=" + port
        # print("connect_string = " + connect_string)
        conn = None
        try:
            # 连接到数据库
            if database_type == "PostgreSQL" or database_type == "openGauss":
                # 创建连接
                conn = psycopg2.connect(
                    dbname=dbname,
                    user=username,
                    password=password,
                    host=host,
                    port=port,
                    connect_timeout=2
                )
                return True
                # # 判断是否连接成功
                # if conn:
                #     print("Successfully connected to PostgreSQL or openGauss database")
                # else:
                #     print("Failed to connect to PostgreSQL or openGauss database")
        except Exception as e:
            print(f"Failed to connect to PostgreSQL database: {e}")
            return False
        finally:
            if conn is not None:
                # 关闭连接
                conn.close()

    # 根据数据库的信息，获取表结构数据
    def init_database_data(self):
        connect_name = str(self.__value["connect_name"])
        database_type = str(self.__value["database_type"])
        dbname = str(self.__value["dbname"])  # db_info_dict["dbname"]
        username = str(self.__value["username"])  # db_info_dict["username"]
        password = str(self.__value["password"])  # db_info_dict["password"]
        host = str(self.__value["host"])  # db_info_dict["host"]
        port = str(self.__value["port"])  # db_info_dict["port"]
        connect_string = "dbname=" + dbname + " user=" + username + " password=" + password + " host=" + host \
                         + " port=" + port
        # print("connect_string = " + connect_string)
        conn = None
        # try:
        # 连接到数据库
        if database_type == "PostgreSQL" or database_type == "openGauss":
            # 创建连接
            conn = psycopg2.connect(
                dbname=dbname,
                user=username,
                password=password,
                host=host,
                port=port,
                connect_timeout=2,
                client_encoding="utf-8"
            )
            if conn:
                print("Successfully connected to PostgreSQL or openGauss database")
                # cursor = conn.cursor()
                cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

                # parent_dir = os.path.dirname(os.path.abspath(__file__))
                # file_path_string_tmp = os.path.join(parent_dir, '..', 'static', 'sql', 'tab_info_v.sql')
                # print("DBUtils.init_database_connection : file_path_string_tmp = " + file_path_string_tmp)
                # sql_script_string_tmp = CommonTool.get_file_content_f(file_path_string_tmp)
                # print("DBUtils.init_database_connection : sql_script_string_tmp = " + sql_script_string_tmp)
                sql_script_string_tmp = """
                select '""" + connect_name + """' as source 
                    ,schema, table_name, table_comment, column_name, data_type 
                    ,column_default as data_default 
                    ,is_nullable as nullable 
                    ,pkey 
                    ,column_comment
                from tab_info_v t
                """
                print("DBUtils.init_database_connection : sql_script_string_tmp = " + sql_script_string_tmp)
                cursor.execute(sql_script_string_tmp)
                rows = cursor.fetchall()
                print("DBUtils.init_database_connection : rows = " + str(rows))

                table_name = "compare_table"
                # self.init_gauss_or_pg_data(table_name, sql_script_string_tmp)

                self.__value["database_type"] = "PostgreSQL"
                self.__value["dbname"] = "tzqdb"
                self.__value["username"] = "tzq"
                self.__value["password"] = "1"
                self.__value["host"] = "127.0.0.1"
                self.__value["port"] = "5432"

                self.common_insert_table_from_data_list(table_name, rows)

                sql_script_string_tmp2 = """
                select '""" + connect_name + """' as source 
                      ,tablename
                      ,indexname
                      ,indexdef
                      ,index_is_primary
                      ,index_is_unique
                      ,index_columns
                      ,where_condition
                  from gs_index_info_v;
                """
                cursor.execute(sql_script_string_tmp2)
                rows = cursor.fetchall()
                table_name = "compare_index_t"
                self.common_insert_table_from_data_list(table_name, rows)

                sql_script_string_tmp3 = """
                select '""" + connect_name + """' as source
                      ,sequencename
                      ,sequence_value_check_result
                      ,repair_scripts
                      ,last_value::nvarchar2
                      ,pkey_max_value::nvarchar2
                      ,increment_by::nvarchar2
                      ,sequence_like_table
                      ,related_table_name
                      ,column_name
                      ,pkey
                  from gs_sequence_info_v;
                """
                cursor.execute(sql_script_string_tmp3)
                rows = cursor.fetchall()
                table_name = "compare_sequence_t"
                self.common_insert_table_from_data_list(table_name, rows)

                # 转换成字典
                # tmp = []
                # for item in rows:
                #     print("DBUtils.init_database_connection : item = " + str(item))
                #     tmp.append(dict(item))
                # print("tmp = " + str(tmp))

                return rows
            else:
                print("Failed to connect to PostgreSQL or openGauss database")
                return None
        # except Exception as e:
        #     print(f"Failed to connect to PostgreSQL database: {e}")
        #     return None
        # finally:
        #     if conn is not None:
        #         # 关闭连接
        #         conn.close()

    def execute_pg_scripts(self, scripts_i: str):
        database_type = str(self.__value["database_type"])
        dbname = str(self.__value["dbname"])  # db_info_dict["dbname"]
        username = str(self.__value["username"])  # db_info_dict["username"]
        password = str(self.__value["password"])  # db_info_dict["password"]
        host = str(self.__value["host"])  # db_info_dict["host"]
        port = str(self.__value["port"])  # db_info_dict["port"]
        connect_string = "dbname=" + dbname + " user=" + username + " password=" + password + " host=" + host \
                         + " port=" + port
        # print("connect_string = " + connect_string)
        conn = None
        if database_type == "PostgreSQL" or database_type == "openGauss":
            # 创建连接
            conn = psycopg2.connect(
                dbname=dbname,
                user=username,
                password=password,
                host=host,
                port=port,
                connect_timeout=2,
                client_encoding="utf-8"
            )
            if conn:
                print("Successfully connected to PostgreSQL or openGauss database")
                # cursor = conn.cursor()
                cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
                cursor.execute(scripts_i)
                rows = cursor.fetchall()
                return rows
            else:
                print("Failed to connect to PostgreSQL or openGauss database")
                return None
        else:
            print("您连接的不是 PostgreSQL 或 openGauss 数据库！")
            return None


if __name__ == '__main__':
    # string = [
    #     {"connect_name": "asdf ", "database_type": "openGauss", "host": "1.1.1.1", "port": "123", "dbname": "水电费",
    #      "user": "阿斯蒂芬", "password": " 发的"}]
    # DBUtils.insert_sqlite3_table_from_data_single("tzq_db_cfg_t", string)

    # print(DBUtils.from_json_to_list(string))

    dbutils = DBUtils()
    dbutils.value["database_type"] = "PostgreSQL"
    dbutils.value["dbname"] = "tzqdb"
    dbutils.value["username"] = "tzq"
    dbutils.value["password"] = "1"
    dbutils.value["host"] = "127.0.0.1"
    dbutils.value["port"] = "5432"

    scripts = "select * from tzq_user_t where lname = 'tzq001'"
    result = dbutils.execute_pg_scripts(scripts)
    print(str(len(result)))
    # print(dbutils.check_database_connection())

